package com.fdb.efp.limit.common.util;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fdb.basic.framework.core.util.ReflectUtility;
import com.fdb.basic.framework.core.util.StringUtil;
import com.fdb.efp.limit.common.annotation.ExcelField;
import com.fdb.efp.limit.common.annotation.ExcelSheet;

/**
 * 
 * @ClassName ExcelImportUtil
 * @Description excel导入工具
 * @author zhangyue
 * @Date 2018年5月4日 上午11:29:13
 * @version 1.0.0
 */
public class ExcelImportUtil {
	private static Logger logger = LoggerFactory.getLogger(ExcelImportUtil.class);

	/**
	 * 从Workbook导入Excel文件，并封装成对象
	 *
	 * @param workbook
	 * @param sheetClass
	 * @return
	 * @throws Exception
	 */
	public static List<Object> importExcel(Workbook workbook, Class<?> sheetClass) throws Exception {
		List<Object> sheetDataList = importSheet(workbook, sheetClass);
		return sheetDataList;
	}

	public static <T> List<T> importExcelByClass(Workbook workbook, Class<?> sheetClass) throws Exception {
		List<T> sheetDataList = importSheetByClass(workbook, sheetClass);
		return sheetDataList;
	}

	@SuppressWarnings("unchecked")
	public static <T> List<T> importSheetByClass(Workbook workbook, Class<?> sheetClass) throws Exception {
		try {
			ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);
			String sheetName = (excelSheet != null && excelSheet.name() != null && excelSheet.name().trim().length() > 0)
					? excelSheet.name().trim()
					: sheetClass.getSimpleName();
			List<Field> fields = new ArrayList<Field>();
			if (sheetClass.getDeclaredFields() != null && sheetClass.getDeclaredFields().length > 0) {
				for (Field field : sheetClass.getDeclaredFields()) {
					if (Modifier.isStatic(field.getModifiers())) {
						continue;
					}
					fields.add(field);
				}
			}
			if (fields == null || fields.size() == 0) {
				throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty.");
			}
			Sheet sheet = workbook.getSheet(sheetName);
			if (sheet == null) {
				throw new Exception("请将导入数据的sheet名称修改为sheet1");
			}
			Iterator<Row> sheetIterator = sheet.rowIterator();
			int rowIndex = 0;
			List<T> dataList = new ArrayList<T>();
			while (sheetIterator.hasNext()) {
				Row rowX = sheetIterator.next();
				if (rowIndex > 2) {
					T rowObj = null;
					for (int i = 0; i < fields.size(); i++) {
						Cell cell = rowX.getCell(i);
						if (cell == null) {
							continue;
						}
						cell.setCellType(CellType.STRING);
						String fieldValueStr = cell.getStringCellValue();
						if (!StringUtil.isNotEmpty(fieldValueStr)) {
							continue;
						}
						Field field = fields.get(i);
						Object fieldValue = FieldReflectionUtil.parseValue(field, fieldValueStr);
						if (fieldValue == null) {
							continue;
						} else {
							if (rowObj == null) {
								rowObj = (T) sheetClass.newInstance();// 需要赋值的时候实例化
							}
							ExcelField excel = field.getDeclaredAnnotation(ExcelField.class);
							if (StringUtil.isNotEmpty(excel.dicType()) && fieldValue.toString().indexOf("-") < 0) {
								throw new Exception("输入的格式应为key-value!");
							} else if (StringUtil.isNotEmpty(excel.dicType()) && fieldValue.toString().indexOf("-") >= 0) {
								fieldValue = fieldValue.toString().split("-")[0];
							}
							ReflectUtility.setObjectFieldValue(rowObj, field, fieldValue);
						}
					}
					if (rowObj != null) {
						dataList.add(rowObj);
					}
				}
				rowIndex++;
			}
			return dataList;
		} catch (IllegalAccessException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		} catch (InstantiationException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		}
	}

	public static List<Object> importSheet(Workbook workbook, Class<?> sheetClass) throws Exception {
		try {
			ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);
			String sheetName = (excelSheet != null && excelSheet.name() != null && excelSheet.name().trim().length() > 0)
					? excelSheet.name().trim()
					: sheetClass.getSimpleName();
			List<Field> fields = new ArrayList<Field>();
			if (sheetClass.getDeclaredFields() != null && sheetClass.getDeclaredFields().length > 0) {
				for (Field field : sheetClass.getDeclaredFields()) {
					if (Modifier.isStatic(field.getModifiers())) {
						continue;
					}
					fields.add(field);
				}
			}
			if (fields == null || fields.size() == 0) {
				throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty.");
			}
			Sheet sheet = workbook.getSheet(sheetName);
			if (sheet == null) {
				throw new Exception("请将导入数据的sheet名称修改为sheet1");
			}
			Iterator<Row> sheetIterator = sheet.rowIterator();
			int rowIndex = 0;
			List<Object> dataList = new ArrayList<Object>();
			while (sheetIterator.hasNext()) {
				Row rowX = sheetIterator.next();
				if (rowIndex > 2) {
					Object rowObj = null;
					for (int i = 0; i < fields.size(); i++) {
						Cell cell = rowX.getCell(i);
						if (cell == null) {
							continue;
						}
						cell.setCellType(CellType.STRING);
						String fieldValueStr = cell.getStringCellValue(); // cell.getCellTypeEnum()
						if (!StringUtil.isNotEmpty(fieldValueStr)) {
							continue;
						}
						Field field = fields.get(i);
						Object fieldValue = FieldReflectionUtil.parseValue(field, fieldValueStr);
						if (fieldValue == null) {
							continue;
						} else {
							if (rowObj == null) {
								rowObj = sheetClass.newInstance();// 需要赋值的时候实例化
							}
							ReflectUtility.setObjectFieldValue(rowObj, field, fieldValue);
						}
					}
					if (rowObj != null) {
						dataList.add(rowObj);
					}
				}
				rowIndex++;
			}
			return dataList;
		} catch (IllegalAccessException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		} catch (InstantiationException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		}
	}

	/**
	 * 导入Excel文件，并封装成对象
	 *
	 * @param excelFile
	 * @param sheetClass
	 * @return
	 * @throws Exception
	 */
	public static List<Object> importExcel(File excelFile, Class<?> sheetClass) throws Exception {
		try {
			Workbook workbook = WorkbookFactory.create(excelFile);
			List<Object> dataList = importExcel(workbook, sheetClass);
			return dataList;
		} catch (IOException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		} catch (InvalidFormatException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		}
	}

	public static <T> List<T> importExcelByClass(File excelFile, Class<?> sheetClass) throws Exception {
		try {
			Workbook workbook = WorkbookFactory.create(excelFile);
			List<T> dataList = importExcelByClass(workbook, sheetClass);
			return dataList;
		} catch (IOException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		} catch (InvalidFormatException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		}
	}

	/**
	 * 从文件路径导入Excel文件，并封装成对象
	 *
	 * @param filePath
	 * @param sheetClass
	 * @return
	 * @throws Exception
	 */
	public static List<Object> importExcel(String filePath, Class<?> sheetClass) throws Exception {
		File excelFile = new File(filePath);
		List<Object> dataList = importExcel(excelFile, sheetClass);
		return dataList;
	}

	/**
	 * 导入Excel数据流，并封装成对象
	 *
	 * @param inputStream
	 * @param sheetClass
	 * @return
	 * @throws Exception
	 */
	public static List<Object> importExcel(InputStream inputStream, Class<?> sheetClass) throws Exception {
		try {
			Workbook workbook = WorkbookFactory.create(inputStream);
			List<Object> dataList = importExcel(workbook, sheetClass);
			return dataList;
		} catch (IOException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		} catch (InvalidFormatException e) {
			logger.error(e.getMessage(), e);
			throw new RuntimeException(e);
		}
	}

}
